package com.qidian.zl.paper.dao.impl;

import com.qidian.zl.paper.dao.AnswerDAO;
import com.qidian.zl.paper.dao.BaseDAO;
import com.qidian.zl.paper.pojo.Answer;
import com.sun.org.glassfish.gmbal.Description;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class AnswerDAOImpl extends BaseDAO implements AnswerDAO {
    @Override
    public int saveAnswers(List<Answer> answers) {
        int result = 0;
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            String sql = "insert into answer(answer_text,answer_time,field_id,paper_id,group_id) " + " values(?,?,?,?,?)";
            con = getCon();
            pst = con.prepareStatement(sql);
            //遍历添加
            for (Answer answer:answers){
                pst.setString(1, answer.getAnswerText());
                pst.setString(2,answer.getAnswerTime());
                pst.setLong(3, answer.getFieldId());
                pst.setLong(4,answer.getPaperId());
                pst.setString(5,answer.getGroupId());
                result+=pst.executeUpdate();
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return result;
    }

    /**
     * @Description:    根据paperId 按groupId 分组  查询
     * @Param: 
     * @return: 
     * @Date: 2021/9/14
     */
    @Override
    public List<Answer> qieryAnswerByPaperId(long paperId ,int page,int size) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        List<Answer> answers = new ArrayList<>();
        String sql = "select * from answer where paper_Id = ? order by group_id limit ?,?";
        try {
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setLong(1, paperId);
            pst.setInt(2,(page-1)*size);
            pst.setInt(3,size);
            rs = pst.executeQuery();
            while (rs.next()) {
                Answer a = new Answer();
                a.setGroupId(rs.getString("group_id"));
                a.setPaperId(rs.getLong("paper_Id"));
                a.setFieldId(rs.getLong("field_id"));
                a.setAnswerTime(rs.getString("answer_time"));
                a.setAnswerText(rs.getString("answer_text"));
                a.setAnswerId(rs.getLong("answer_id"));
                answers.add(a);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return answers;
    }

    @Override
    public int queryTotal(long paperId) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int total = 0;
        String sql="select count(*) from answer where paper_id=?";
        try {
            con=getCon();
            pst=con.prepareStatement(sql);
            pst.setLong(1,paperId);
            rs = pst.executeQuery();
            if (rs.next()){
              total= rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            closeAll(rs,pst,con);
        }
        return total;
    }

    @Override
    public int deleteByGroupId(String groupId) {
        int result = 0;
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try{
            String sql = "delete from answer where group_id = ?";
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setString(1,groupId);
            result = pst.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            closeAll(rs,pst,con);
        }
        return result;
    }

    @Override
    public List<Answer> queryAnswerByTime(long paperId,String startTime, String endTime,int page,int size) {

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        List<Answer> answers = new ArrayList<>();
        String sql = "select * from answer where 1 = 1 ";
            if(startTime!=null && !"".equals(startTime)){
                sql += " and answer_time > '"+startTime+"'";
            }
            if(endTime !=null && !"".equals(endTime)){
                sql += " and answer_time < '"+endTime+"'";
        }
        sql += " and paper_id=? limit ?,?";
        try {
            con = getCon();
            pst = con.prepareStatement(sql);
            pst.setLong(1,paperId);
            pst.setInt(2,(page-1)*size);
            pst.setInt(3,size);
            rs = pst.executeQuery();
            while (rs.next()) {
                Answer a = new Answer();
                a.setGroupId(rs.getString("group_id"));
                a.setPaperId(rs.getLong("paper_Id"));
                a.setFieldId(rs.getLong("field_id"));
                a.setAnswerTime(rs.getString("answer_time"));
                a.setAnswerText(rs.getString("answer_text"));
                a.setAnswerId(rs.getLong("answer_id"));
                answers.add(a);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            closeAll(rs, pst, con);
        }
        return answers;
    }

    @Override
    public int queryTotal(long paperId,String startTime, String endTime) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int total = 0;

        String sql="select count(*) from answer where 1=1";
        if(startTime!=null && !"".equals(startTime)){
            sql += " and answer_time > '"+startTime+"'";
        }
        if(endTime !=null && !"".equals(endTime)){
            sql += " and answer_time < '"+endTime+"'";
        }
        sql+=" and paper_id=?";
        try {
            con=getCon();
            pst=con.prepareStatement(sql);
            pst.setLong(1,paperId);
            rs = pst.executeQuery();
            if (rs.next()){
                total= rs.getInt(1);
            }
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            closeAll(rs,pst,con);
        }
        return total;
    }
}
